package com.jxc.jxc01.hch.dao.Impl;

import com.jxc.jxc01.hch.dao.WareHouseDao;
import com.jxc.jxc01.hch.entity.JDBCConn;
import com.jxc.jxc01.hch.entity.PageHelper;
import com.jxc.jxc01.hch.entity.User;
import com.jxc.jxc01.hch.entity.WareHouse;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class WareHouseDaoImpl implements WareHouseDao {
    /**
     * 查询仓库
     * @param pageHelper
     * @return
     */
    @Override
    public PageHelper<WareHouse> list(PageHelper<WareHouse> pageHelper) {
        List<WareHouse> list = new ArrayList<>();
        try {
            //加载驱动类
            JDBCConn jdbcConn = new JDBCConn();
            String sql =  "select * from warehouse limit ?,?";
            PreparedStatement stat=jdbcConn.connect(sql);
            stat.setInt(1,
                    (pageHelper.getPageNum()-1)*pageHelper.getPageSize());
            stat.setInt(2,pageHelper.getPageSize());
            ResultSet rs= stat.executeQuery();
            while (rs.next()){
                WareHouse wareHouse = new WareHouse();
                wareHouse.setId(rs.getInt("id"));
                wareHouse.setName(rs.getString("warehousename"));
                wareHouse.setHeadname(rs.getString("headname"));
                wareHouse.setPhone(rs.getString("phone"));
                wareHouse.setAddress(rs.getString("address"));
                wareHouse.setSize(rs.getInt("size"));
                wareHouse.setUnit(rs.getString("unit"));
                String creationtime =rs.getString("creationtime");
                wareHouse.setCreationtime(creationtime);
                wareHouse.setState(rs.getString("state"));
                wareHouse.setNotes(rs.getString("notes"));
                list.add(wareHouse);

            }
            pageHelper.setData(list);
            String sql1="select count(*) from warehouse";
            PreparedStatement stat1 = jdbcConn.connect(sql1);
            ResultSet rs1 = stat1.executeQuery();
            if (rs1.next()){
                int count =rs1.getInt("count(*)");
                pageHelper.setTotal(count);
            }
            rs1.close();
            stat1.close();
            rs.close();
            stat.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return pageHelper;

    }

    /**
     * 模糊查询
     * @param pageHelper
     * @param username
     * @return
     */
    @Override
    public PageHelper<WareHouse> query(PageHelper<WareHouse> pageHelper, String username) {
        List<WareHouse> list = new ArrayList<>();
        try {
            //加载驱动类
            JDBCConn jdbcConn = new JDBCConn();
            String sql =  "select * from warehouse where warehousename like ? limit ?,?";
            PreparedStatement stat=jdbcConn.connect(sql);
            stat.setString(1,"%"+username+"%");
            stat.setInt(2,
                    (pageHelper.getPageNum()-1)*pageHelper.getPageSize());
            stat.setInt(3,pageHelper.getPageSize());
            ResultSet rs= stat.executeQuery();
            while (rs.next()){
                WareHouse wareHouse = new WareHouse();
                wareHouse.setId(rs.getInt("id"));
                wareHouse.setName(rs.getString("warehousename"));
                wareHouse.setHeadname(rs.getString("headname"));
                wareHouse.setPhone(rs.getString("phone"));
                wareHouse.setAddress(rs.getString("address"));
                wareHouse.setSize(rs.getInt("size"));
                wareHouse.setUnit(rs.getString("unit"));
                wareHouse.setCreationtime(rs.getString("creationtime"));
                wareHouse.setState(rs.getString("state"));
                wareHouse.setNotes(rs.getString("notes"));
                list.add(wareHouse);
            }
            pageHelper.setData(list);
            String sql1="select count(*) from warehouse where warehousename like ?";
            PreparedStatement stat1 = jdbcConn.connect(sql1);
            stat1.setString(1,"%"+username+"%");
            ResultSet rs1 = stat1.executeQuery();
            if (rs1.next()){
                int count =rs1.getInt("count(*)");
                pageHelper.setTotal(count);
            }
            rs1.close();
            stat1.close();
            rs.close();
            stat.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return pageHelper;
    }

    /**
     * 添加仓库
     * @param wareHouse
     * @return
     */
    @Override
    public int increase(WareHouse wareHouse) {
        int up=0;
        try {
            JDBCConn jdbcConn = new JDBCConn();
            String sql ="insert into warehouse(id,warehousename,headname,phone,address,size,unit,Creationtime,state,notes) values (?,?,?,?,?,?,?,?,?,?)";
            PreparedStatement stat = jdbcConn.connect(sql);
            stat.setInt(1,wareHouse.getId());
            stat.setString(2,wareHouse.getName());
            stat.setString(3,wareHouse.getHeadname());
            stat.setString(4,wareHouse.getPhone());
            stat.setString(5,wareHouse.getAddress());
            stat.setInt(6,wareHouse.getSize());
            stat.setString(7,wareHouse.getUnit());
            stat.setString(8,wareHouse.getCreationtime());
            stat.setString(9,wareHouse.getState());
            stat.setString(10,wareHouse.getNotes());
            up = stat.executeUpdate();
            stat.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        //创建连接对象
        return up;
    }

    /**
     * 修改仓库字段
     * @param wareHouse
     * @return
     */
    @Override
    public int modify(WareHouse wareHouse) {
        int up;
        try {
            JDBCConn jdbcConn = new JDBCConn();
            String sql = "update warehouse set warehousename=?,headname=?,phone=?,address=?,size=?,unit=?,state=?,notes=? where id=?";
            PreparedStatement stat = jdbcConn.connect(sql);
            stat.setString(1,wareHouse.getName());
            stat.setString(2,wareHouse.getHeadname());
            stat.setString(3,wareHouse.getPhone());
            stat.setString(4,wareHouse.getAddress());
            stat.setInt(5,wareHouse.getSize());
            stat.setString(6,wareHouse.getUnit());
            stat.setString(7,wareHouse.getState());
            stat.setString(8,wareHouse.getNotes());
            stat.setInt(9,wareHouse.getId());
            up = stat.executeUpdate();
            stat.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return up;
    }

    /**
     * 删除仓库
     * @param id
     * @return
     */
    @Override
    public int delete(int id) {
        int delete=0;
        try {
            JDBCConn jdbcConn = new JDBCConn();
            String sql = "delete from warehouse where id=?";
            PreparedStatement stat = jdbcConn.connect(sql);
            stat.setInt(1,id);
            delete= stat.executeUpdate();
            stat.close();
        }catch (Exception e){

        }
        return delete;
    }
}
